﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using PgBirdie.Core.Config;
using PgBirdie.Core.Model;
using PgBirdie.Core.Model.Database.Data;
using PgBirdie.Core.Model.Database.Structure;
using PgBirdie.Core.Model.Database.Structure.Constraint;
using PgBirdie.Core.Service.DbObject;
using PgBirdie.Core.Service.Infrastructure;

namespace PgBirdie.Core.Service.SourceControl
{
    public class DbFromSourceControlService : IDbFromSourceControlService
    {
        private readonly IDbObjectFromSourceControlService _dbObject;
        private readonly IFileSystemService _file;
        private readonly ITextFormatService _text;
        private readonly IDbObjectValidator _validator;

        public DbFromSourceControlService(IDbObjectFromSourceControlService dbObject, IFileSystemService file, ITextFormatService text, IDbObjectValidator validator)
        {
            _dbObject = dbObject;
            _file = file;
            _text = text;
            _validator = validator;
        }

        public PgDatabase GetDbStructure()
        {
            var dbName = Settings.Source.Database;
            var dbPath = String.Format("{0}{1}", Settings.PathTo.Dump, dbName);

            var dbFilePath = String.Format(@"{0}\database.sql", dbPath);
            if (!File.Exists(dbFilePath))
                throw new FileNotFoundException("{0} was not found. It must exist for processing to continue.", dbFilePath);

            var db = GetDatabaseStructure(_file.ReadFile(dbFilePath));

            var schemaNames = _file.GetLastSegmentsFromPaths(_file.GetDirectoryNames(dbPath).ToArray());
            
            foreach (var schemaName in schemaNames)
            {
                var schemaFilePath = String.Format(@"{0}\{1}\schema.sql", dbPath, schemaName);
                if (!File.Exists(schemaFilePath))
                    throw new FileNotFoundException("{0} was not found. It must exist for processing to continue.");

                var schema = GetSchemaStructure(_file.ReadFile(schemaFilePath), db);

                var sequenceFilePaths = _file.GetFilenames(String.Format(@"{0}\{1}\{2}", dbPath, schema.Name, Settings.Misc.DirNameForSequences));

                foreach (var path in sequenceFilePaths)
                {
                    if (!File.Exists(path))
                        throw new FileNotFoundException(path + " was not found. It must exist for processing to continue.");

                    var sequence = GetSequenceStructure(_file.ReadFile(path), schema);

                    schema.Sequences.Add(sequence.Name, sequence);
                }

                var tableFilePaths = _file.GetFilenames(String.Format(@"{0}\{1}\{2}", dbPath, schema.Name, Settings.Misc.DirNameForTables));

                foreach (var path in tableFilePaths)
                {
                    if (!File.Exists(path))
                        throw new FileNotFoundException(path + " was not found. It must exist for processing to continue.");

                    var table = GetTableStructure(_file.ReadFile(path), schema);
                    table.Schema = schema;

                    schema.Tables.Add(table.Name, table);
                }

                db.Schemas.Add(schema.Name, schema);
            }

            return db;
        }

        public void PopulateStaticData(ref PgDatabase db)
        {
            var dbName = Settings.Source.Database;
            var rootPath = String.Format("{0}{1}", Settings.PathTo.Dump, dbName);

            var schemaNames = _file.GetLastSegmentsFromPaths(_file.GetDirectoryNames(rootPath).ToArray());

            foreach (var schemaName in schemaNames)
            {
                if (!db.Schemas.ContainsKey(schemaName))
                    continue;

                var path = String.Format(@"{0}\{1}\{2}", rootPath, schemaName,
                                         Settings.Misc.DirNameForStaticData);

                var tableNames =
                    _file.GetLastSegmentsFromPaths(_file.GetFilenames(path)).Select(c => c.Substring(0, c.Length - 4));                

                foreach (var tableName in tableNames)
                {
                    if (!db.Schemas[schemaName].Tables.ContainsKey(tableName))
                        continue;

                    var table = db.Schemas[schemaName].Tables[tableName];

                    var text = File.ReadAllText(String.Format("{0}\\{1}.sql", path, table.Name), Encoding.UTF8);
                    db.Schemas[schemaName].Tables[tableName].DataRows = GetTableStaticData(table, text);
                }
            }
        }
  
        private PgDatabase GetDatabaseStructure(string text)
        {
            var db = new PgDatabase();

            var totalLines = text.Split(Settings.SplitterFor.NewLine, StringSplitOptions.RemoveEmptyEntries);
            var totalCount = totalLines.Length;
            
            for (var i = 0; i < totalCount; i++)
            {
                var line = _text.RemoveCommaOrSemiColonFromTheEnd(totalLines[i].Trim());

                if (SkipLine(line))
                    continue;

                if (line.StartsWith("CREATE"))
                {
                    db = _dbObject.GetDatabase(line);
                    //db.OrderNo = orderNo;
                    //orderNo++;
                    continue;
                }

                var lineSegments = line.Split(Settings.SplitterFor.Space, StringSplitOptions.RemoveEmptyEntries);

                if (lineSegments[3].Equals("OWNER"))
                {
                    db.Owner = _dbObject.GetOwner(line, db);
                    continue;
                }

                if (line.StartsWith("REVOKE") || line.StartsWith("GRANT"))
                {
                    var privilege = _dbObject.GetPrivilege(line, db);
                    db.Privileges.Add(privilege.Id, privilege);
                    continue;
                }
            }

            return db;
        }

        private PgSchema GetSchemaStructure(string text, PgDatabase db)
        {
            var schema = new PgSchema();

            var totalLines = text.Split(Settings.SplitterFor.NewLine, StringSplitOptions.RemoveEmptyEntries);
            var totalCount = totalLines.Length;

            for (var i = 0; i < totalCount; i++)
            {
                var line = _text.RemoveCommaOrSemiColonFromTheEnd(totalLines[i].Trim());

                if (SkipLine(line))
                    continue;

                if (line.StartsWith("CREATE"))
                {
                    schema = _dbObject.GetSchema(line);
                    schema.Database = db; // Associate with database.
                    //db.OrderNo = orderNo;
                    //orderNo++;
                    continue;
                }

                var lineSegments = line.Split(Settings.SplitterFor.Space, StringSplitOptions.RemoveEmptyEntries);

                if (lineSegments[3].Equals("OWNER"))
                {
                    schema.Owner = _dbObject.GetOwner(line, schema);
                    continue;
                }

                if (line.StartsWith("REVOKE") || line.StartsWith("GRANT"))
                {
                    var privilege = _dbObject.GetPrivilege(line, schema);
                    schema.Privileges.Add(privilege.Id, privilege);
                    continue;
                }
            }

            return schema;
        }

        private PgSequence GetSequenceStructure(string text, PgSchema schema)
        {
            var sequence = new PgSequence();

            var totalLines = text.Split(Settings.SplitterFor.NewLine, StringSplitOptions.RemoveEmptyEntries);
            var totalCount = totalLines.Length;

            for (var i = 0; i < totalCount; i++)
            {
                var line = _text.RemoveCommaOrSemiColonFromTheEnd(totalLines[i].Trim());

                if (SkipLine(line))
                    continue;

                var segments = line.Split(Settings.SplitterFor.Space, StringSplitOptions.RemoveEmptyEntries);
                var segmentCount = segments.Length;

                if (line.StartsWith("CREATE SEQUENCE"))
                {
                    sequence.ModelType = PgDbObjectType.Sequence;
                    sequence.Schema = schema; // Associate with schema.
                    sequence.Name = _dbObject.GetSequenceName(line);
                    sequence.StartWith = _dbObject.GetSequenceStartWith(totalLines[i + 1].Trim());
                    sequence.IncrementBy = _dbObject.GetSequenceIncrementBy(totalLines[i + 2].Trim());
                    sequence.MinValue = _dbObject.GetSequenceMinValue(totalLines[i + 3].Trim());
                    sequence.MaxValue = _dbObject.GetSequenceMaxValue(totalLines[i + 4].Trim());
                    sequence.Cache = _dbObject.GetSequenceCache(totalLines[i + 5].Trim());

                    continue;
                }

                if (segmentCount > 3 && segments[3].Equals("OWNER"))
                {
                    sequence.Owner = _dbObject.GetOwner(line, sequence);
                    continue;
                }

                if (line.StartsWith("REVOKE") || line.StartsWith("GRANT"))
                {
                    var privilege = _dbObject.GetPrivilege(line, sequence);
                    sequence.Privileges.Add(privilege.Id, privilege);
                    continue;
                }
            }

            return sequence;
        }

        private PgTable GetTableStructure(string text, PgSchema schema)
        {
            var table = new PgTable();

            var totalLines = text.Split(Settings.SplitterFor.NewLine, StringSplitOptions.RemoveEmptyEntries);
            var totalCount = totalLines.Length;

            for (var i = 0; i < totalCount; i++)
            {
                var line = _text.RemoveCommaOrSemiColonFromTheEnd(totalLines[i].Trim());

                if (SkipLine(line))
                    continue;

                var segments = line.Split(Settings.SplitterFor.Space, StringSplitOptions.RemoveEmptyEntries);
                var segmentCount = segments.Length;

                if (line.StartsWith("CREATE TABLE") || line.StartsWith("CREATE UNLOGGED"))
                {
                    table = _dbObject.GetTable(line);
                    table.Schema = schema; // Associate with schema.
                    table.IsUnlogged = segments[1].Equals("UNLOGGED") ? true : false;

                    var subIndex = i + 1;
                    for (var i2 = subIndex; i2 < totalCount; i2++)
                    {
                        var colLine = totalLines[i2].Trim();

                        // Marks end of table definition.
                        if (colLine.StartsWith(")"))
                        {
                            i = i2; // Set the processing to continue after end of table definition.
                            break;
                        }

                        // When line starts with a double quote (") it refers to a column.
                        var column = _dbObject.GetColumn(colLine);
                        column.Table = table;
                        //column.OrderNo = orderNo;

                        table.Columns.Add(column.Name, column);

                        //orderNo++;
                    }

                    continue;
                }

                if (segments.Length >= 9 && segments[8].StartsWith("nextval"))
                {
                    var columnName = segments[5].Replace("\"", String.Empty);
                    var sequenceName = _dbObject.GetColumnSequenceName(segments[segments.Length - 1]);

                    table.Columns[columnName].SequenceName = sequenceName;
                }

                if (segmentCount > 2 && (segments[1].Equals("INDEX") || segments[2].Equals("INDEX")))
                {
                    var nextLine = totalLines[i + 1];
                    bool isClustered = !String.IsNullOrEmpty(nextLine) && nextLine.StartsWith("CLUSTER");

                    var index = _dbObject.GetIndex(line, segments, isClustered);
                    index.Table = table;

                    // Validate index.
                    _validator.ValidateIndex(index);
                    
                    table.Indexes.Add(index.Name, index);

                    continue;
                }

                if (segmentCount > 4 && line.StartsWith("ALTER") && segments[4].Equals("CONSTRAINT"))
                {
                    var constraint = _dbObject.GetConstraint(segments.ToList(), table);
                    constraint.ModelType = PgDbObjectType.Constraint;

                    if (constraint.ConstraintType == ConstraintType.PrimaryKey)
                        table.PrimaryKey = constraint as PgPrimaryKey;
                    else if (constraint.ConstraintType == ConstraintType.UniqueKey)
                        table.UniqueKeys.Add(constraint.Name, constraint as PgUniqueKey);
                    else if (constraint.ConstraintType == ConstraintType.ForeignKey)
                        table.ForeignKeys.Add(constraint.Name, constraint as PgForeignKey);

                    continue;
                }

                if (segmentCount > 3 && segments[3].Equals("OWNER"))
                {
                    table.Owner = _dbObject.GetOwner(line, table);
                    continue;
                }

                if (line.StartsWith("REVOKE") || line.StartsWith("GRANT"))
                {
                    var privilege = _dbObject.GetPrivilege(line, table);
                    table.Privileges.Add(privilege.Id, privilege);
                    continue;
                }
            }

            return table;
        }

        private IDictionary<string, PgDataRow> GetTableStaticData(PgTable table, string text)
        {
            var dataRows = new Dictionary<string, PgDataRow>();

            var allLines = text.Split(Settings.SplitterFor.WindowsLineBreak, StringSplitOptions.None);

            var totalCount = allLines.Length;
            for (var i = 0; i < totalCount; i++)
            {
                var line = allLines[i].Trim();

                if (SkipLine(line))
                    continue;                

                if (line.StartsWith("INSERT"))
                {
                    string staticData;
                    var dataColumns = new Dictionary<string, PgDataColumn>();

                    var startIndex = table.Name.Length + 23; // Drops "INSERT... (" from the start.

                    if (line.EndsWith(";")) // Single line INSERT.
                    {
                        var length = line.Length - (startIndex + 2); // Drops ");" from the end.
                        staticData = line.Substring(startIndex, length);
                    }
                    else // Multiline INSERT.
                    {
                        var lines = new List<string>()
                            {
                                // Drops "INSERT... (" from the start.
                                line.Substring(startIndex, line.Length - startIndex)
                            };

                        var subIndex = i + 1;
                        for (var i2 = subIndex; i < totalCount; i2++)
                        {
                            var dataLine = allLines[i2];
                            var nextLine = String.Empty;
                            if (allLines.Length < i2 + 1)
                                nextLine = allLines[i2 + 1];

                            // Marks end of multiline static data definition.
                            if (dataLine.EndsWith(");") && (nextLine.StartsWith("INSERT") || String.IsNullOrEmpty(nextLine)))
                            {
                                dataLine = dataLine.Remove(dataLine.Length - 2, 2); // Drops ");" from the end.
                                lines.Add(dataLine);
                                i = i2;
                                break;
                            }

                            lines.Add(dataLine);
                        }

                        staticData = String.Join(Environment.NewLine, lines.ToArray());
                    }

                    string[] values = Regex.Split(staticData, Settings.SplitterFor.StaticData);

                    // If this pops up, something's wrong with the Regex.Split; # of columns/values should match.
                    if (table.Columns.Count != values.Length)
                        throw new Exception("Could not handle static data INSERT statement.");

                    var counter = 0;
                    foreach (var column in table.Columns.Values)
                    {
                        var dataColumn = new PgDataColumn(column, values[counter]);
                        dataColumns.Add(dataColumn.Name, dataColumn);

                        counter++;
                    }

                    var dataRow = new PgDataRow(table, dataColumns);                    
                    dataRows.Add(dataRow.Uid, dataRow);
                }
            }

            return dataRows;
        }

        private bool SkipLine(string text)
        {
            // Skip irrelevant lines. Skips BEGIN/COMMIT.
            if (String.IsNullOrEmpty(text) || text.Length < 13)
                return true;
            return false;
        }
    }
}